package com.gwclh.personnel.mapper;

import com.gwclh.personnel.entity.Employee;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 员工表 Mapper 接口
 * </p>
 *
 * @author gzhhh
 * @since 2020-07-24
 */
public interface EmployeeMapper extends BaseMapper<Employee> {

    /**
     * 根据公司统计该公司各部门的月入职数
     *
     * @param pId 公司id
     */
    @Select("SELECT DATE_FORMAT(e.emp_date,'%Y-%m') as time, count(*) as total ,d.dept_name "
            + "FROM t_department as d,t_employee as e WHERE d.p_id = #{pId} and e.dept_id = d.id "
            + "and DATE_FORMAT(e.emp_date,'%Y%m') = DATE_FORMAT( CURDATE(),'%Y%m' ) GROUP BY time,e.dept_id")
    List<Map<String, Object>> statisticsEntryEmpByCompany(Integer pId);

    /**
     * 查询总公司入职人数
     */
    @Select("SELECT DATE_FORMAT(emp_date,'%Y-%m') as time,count(*) as total from t_employee WHERE DATE_FORMAT(emp_date,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') and is_talents=0 GROUP BY time")
    Map<String, Object> statisticsEntryEmpMonth();

    /**
     * 统计某公司各部门的员工总数
     *
     * @param pId
     * @return
     */
    @Select("SELECT d.dept_name as name,COUNT(*) as value FROM t_department as d,t_employee as e WHERE d.p_id = #{pId} and e.dept_id = d.id AND e.is_talents = 0 GROUP BY e.dept_id")
    List<Map<String, Object>> statisticsDeptEmpByCompany(Integer pId);
    // Map<String, Object> statisticsEntryEmpMonth();

    /**
     * 统计每个月转正的人数
     *
     * @return
     */
    @Select("SELECT DATE_FORMAT(i.oper_date,'%m') month, count(*) as total FROM t_internship i where i.oper_date is NOT NULL GROUP BY month")
    List<Map<String, Object>> selectInternshipMonthData();

    /**
     * 统计每个岗位的人数
     *
     * @return
     */
    @Select("SELECT COUNT(*) as count,s.sta_name FROM t_employee e LEFT JOIN t_station s on e.sta_id = s.id GROUP BY e.sta_id")
    List<Map<String, Object>> selectStationData();

    /**
     * 统计公司每个月每个部门的离职人数
     *
     * @param id
     * @return
     */
    @Select("SELECT DATE_FORMAT(d.dis_date,'%Y-%m') as date, de.dept_name as departmentName, COUNT(*) as count "
            + "from t_dismission d,t_department de,t_employee e " + "where d.emp_id = e.id " + "AND e.dept_id = de.id "
            + "AND DATE_FORMAT(d.dis_date,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') " + "AND de.p_id = #{id} GROUP BY de.id\n")
    List<Map<String, Object>> selectDismissionDateByDepartment(Integer id);

    /**
     * 统计岗位在职人数
     *
     * @return
     */
    @Select("SELECT s.sta_name as name ,count(*) as value FROM t_employee as e,t_station as s WHERE e.`status` = 1 and e.sta_id = s.id GROUP BY e.sta_id")
    List<Map<String, Object>> statisticsStationEmp();

    @Select("SELECT COUNT(*) as count " +
            "FROM t_employee e,t_department de " +
            "WHERE e.dept_id = de.id " +
            "AND DATE_FORMAT(e.emp_date,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') " +
            "AND de.id = #{id}")
    Map<String, Object> selectEntryDataByDeptId(Integer id);
}
